今天繼續介紹update/delete及query語法，基本上主體上還是離不開SQL，query部分，配合closure可以很方便的處理查詢的資料，另外Groovy另有提供Groovy.sql.Sql的subclass,Groovy.sql.DataSet，DataSet所提供的方法就比較人性化一點，比較少SQL語法的影子，

以下是今天的分享
update/delete用的還是昨天的Table，query則換一個資料比較多的Table當作範例。
昨天的資料內容

update/delete範例如下:

import groovy.sql.Sql 

def db=Sql.newInstance("jdbc:mysql://localhost/goo", 
                                            "root",    
                                            "lga2011", 
                                            "com.mysql.jdbc.Driver") 
//標準SQL delete語法                                        
db.execute('''
    DELETE FROM openitem WHERE sourceNo='FPR-11-0089'
''')

String correctNo='FPR-12-0587'
String wrongNo='FPR-12-0458'
//標準SQL update語法，用GString不容易打錯
db.execute("""
    UPDATE openitem SET sourceNo=$correctNo WHERE sourceNo=$wrongNo  
""")

update/delete結果:

從資料庫取得資料，Groovy除了execute方法外，還有以下方法

void  eachRow  String statement { row -> code }
void  eachRow  String prepStmt, List values { row -> code }
void  eachRow  GString prepStmt { row -> code }
void  query  String statement { resultSet -> code }
void  query  String prepStmt, List values { resultSet -> code }
void  query  GString prepStmt { resultSet -> code }
List  rows  String statement
List  rows  String prepStmt, List values
List  rows  GString prepStmt
Object  firstRow  String statement
Object  firstRow  String prepStmt, List values
Object  firstRow  GString prepStmt

Groovy JDBC第一天已經分享過eachRow如何使用
接下來分享query方法如何使用，query方法用到java.sql.ResultSet觀念，而rows預設回傳List物件，分享如下

package myGroovy 
import groovy.sql.Sql 

def db=Sql.newInstance("jdbc:mysql://localhost/goo", 
                                            "root",    
                                            "lga2011", 
                                            "com.mysql.jdbc.Driver") 
                                            
String queryString="SELECT batchno, fddr, rev, source FROM batchtable WHERE source like '%FPR%'"
println '使用query語法'
db.query (queryString){rs->
    if(rs.next()){//跟java resultset語法相同
        println rs.getString(1)+', '+rs.getString('fddr')+', '+
                    rs.getInt(3)+', '+rs.getString(4)
    }
}

print '\n'                                        
println '使用List語法'
List batches=db.rows(queryString+' AND batchno like "B-017"') //db.rows預設傳回List物件
println "There are ${batches.size()} FPRs in Batch 17"
println batches.collect{
    "${it[0]} ${it.source}"}.join(", ") //join顧名思義就是把資料用(", ")串起來
batches.each{println "${it.source} is associated with ${it[0]}"}

print '\n'
println '使用分頁功能(Pagination)'
List onlyTop10records =db.rows(queryString, 1, 10) //指定取得前10筆資料
println "There are ${onlyTop10records.size()} FPRs in Page 1"
onlyTop10records.each{
    println "Page 1 ${it.source} is associated with ${it[0]}"}

print '\n'
println '使用query語法取得meta資料'
db.query(queryString){rs->
    def meta=rs.metaData //取得metaData物件
    if(meta.columnCount>=0){
        for(i in 0..<meta.columnCount){
            print "Table Column ${i}: ${meta.getColumnLabel(i+1)}"
            .padRight(25) //padRight(x)就是padding多少....要多少可以自己try
        }
    }else{
        return
    }
}

執行結果:

使用query語法
B-001, FDDR LT0-00932, 0, FPR-12-0293

使用List語法
There are 5 FPRs in Batch 17
B-017 FPR-12-0292-RB, B-017 FPR-08-0021-RA, B-017 FPR-12-0615, B-017 FPR-12-0598, B-017 FPR-12-0042
FPR-12-0292-RB is associated with B-017
FPR-08-0021-RA is associated with B-017
FPR-12-0615 is associated with B-017
FPR-12-0598 is associated with B-017
FPR-12-0042 is associated with B-017

使用分頁功能(Pagination)
There are 10 FPRs in Page 1
Page 1 FPR-12-0293 is associated with B-001
Page 1 FPR-10-0217-RA is associated with B-001
Page 1 FPR-11-0841 is associated with B-001
Page 1 FPR-11-0820 is associated with B-001
Page 1 FPR-11-0848 is associated with B-001
Page 1 FPR-09-0210 is associated with B-001
Page 1 FPR-11-0793 is associated with B-001
Page 1 FPR-12-0293 is associated with B-001
Page 1 FPR-10-0744-RD is associated with B-001
Page 1 FPR-09-0126-RB is associated with B-001

使用query語法取得meta資料
Table Column 0: batchno  Table Column 1: fddr     Table Column 2: rev      Table Column 3: source   

關於Transition我想在分享GORM in Grails的時候在跟大家分享，另外DataSet部分明天在跟大家分享